
from apps.common.utils.time import getFormatTime
from apps.common.utils.tools import random_num
from apps.common.uploadFile.serviceOfoss import baseImgUrl, bucket, filePath
from xlwt import CompoundDoc
import xlwt
import xlrd
import datetime
import json


# 导出表格
def exportExcel(_itemOrders):
    workbook = xlwt.Workbook(encoding = 'utf-8')
    worksheet = workbook.add_sheet('订单明细')

    style = xlwt.XFStyle()
    #设置样式
    alignment = xlwt.Alignment()
    # 水平位置：0x01(左端对齐)、0x02(水平方向上居中对齐)、0x03(右端对齐)
    alignment.horz = 0x01
    # 垂直方向：0x00(上端对齐)、 0x01(垂直方向上居中对齐)、0x02(底端对齐)
    alignment.vert = 0x01
    style.alignment = alignment
    #设置字体
    font = xlwt.Font()
    # font.bold = True
    style.font = font

    # worksheet.col(0).width = 4000
    # worksheet.col(1).width = 3000
    # worksheet.col(2).width = 4000
    # worksheet.col(3).width = 4000

    # row_first = ['订单编号', '购买用户ID', '购买用户手机号', '订单状态', '提货方式', '收货人', '收货人手机号', '送货地址', '买家留言', '支付方式', '支付时间', '下单时间', '订单总金额', '商品名称', '商品规格', '单价', '商品数量']
    row_first = ['订单编号', '购买用户ID', '购买用户手机号', '收货人', '收货人手机号', '送货地址', '买家留言', '支付方式', '订单总金额', '商品名称', '商品规格', '商品数量']
    # 设置第一行
    for col_index, title in enumerate(row_first):
        style1 = xlwt.XFStyle()
        font1 = xlwt.Font()
        font1.bold = True
        style1.font = font1
        worksheet.write(0, col_index, title, style1)
    row_index = 1
    for row_i, record in enumerate(_itemOrders):
        last_col_index = 0
        for col_index, title in enumerate(row_first):
            if title == '订单编号':
                worksheet.write(row_index, col_index, record.orderId, style)
                worksheet.col(col_index).width = 6000
            if title == '购买用户ID':
                worksheet.write(row_index, col_index, record.user.code if record.user else '', style)
                worksheet.col(col_index).width = 2000
            if title == '购买用户手机号':
                worksheet.write(row_index, col_index, record.user.phone if record.user else '', style)
                worksheet.col(col_index).width = 3000
            if title == '订单状态':
                worksheet.write(row_index, col_index, record.get_orderStatus_display() or '未知', style)
                worksheet.col(col_index).width = 2000
            if title == '提货方式':
                worksheet.write(row_index, col_index, record.get_sendType_display() or '未知', style)
                worksheet.col(col_index).width = 2000
            addrinfo = {
                'contactName': '',
                'contactPhone': '',
                'addrInfo': ''
            }
            if record.addrinfo:
                addrinfo = json.loads(record.addrinfo)
            if title == '收货人':
                worksheet.write(row_index, col_index, addrinfo.get('contactName'), style)
                worksheet.col(col_index).width = 2000
            if title == '收货人手机号':
                worksheet.write(row_index, col_index, addrinfo.get('contactPhone'), style)
                worksheet.col(col_index).width = 4000
            if title == '送货地址':
                worksheet.write(row_index, col_index, addrinfo.get('addrInfo'), style)
                worksheet.col(col_index).width = 6000
            if title == '买家留言':
                worksheet.write(row_index, col_index, record.remark, style)
                worksheet.col(col_index).width = 4000
            if title == '支付方式':
                worksheet.write(row_index, col_index, record.get_payway_display() or '未知', style)
                worksheet.col(col_index).width = 2000
            if title == '支付时间':
                payTime = str(record.payTime)
                if payTime and len(payTime)>=19:
                    payTime = payTime[0:10] + ' ' + payTime[11:19]
                else:
                    payTime = ''
                worksheet.write(row_index, col_index, payTime, style)
                worksheet.col(col_index).width = 5000
            if title == '下单时间':
                createTime = str(record.createTime)
                if createTime and len(createTime)>=19:
                    createTime = createTime[0:10] + ' ' + createTime[11:19]
                else:
                    createTime = ''
                worksheet.write(row_index, col_index, createTime, style)
                worksheet.col(col_index).width = 5000
            if title == '订单总金额':
                worksheet.write(row_index, col_index, record.realPay, style)
                worksheet.col(col_index).width = 2000
                last_col_index = col_index
                
        content = json.loads(record.content)
        if len(content)>0:
            for item in content:
                worksheet.write(row_index, last_col_index+1, item.get('itemName'), style)
                worksheet.col(last_col_index+1).width = 6000
                try:
                    if item.get('specValues'):
                        specValues = json.loads(item.get('specValues'))
                        specValueStr = ''
                        for specValue in specValues:
                            specValueStr = specValueStr + specValue.get('specName') + '：' + specValue.get('name') + '，'
                        if len(specValueStr)>0:
                            specValueStr = specValueStr[0:len(specValueStr)-1]
                        worksheet.write(row_index, last_col_index+2, specValueStr, style)
                        worksheet.col(last_col_index+2).width = 6000
                except Exception as e:
                    print('e', e)
        
                # worksheet.write(row_index, last_col_index+3, item.get('price'), style)
                # worksheet.col(last_col_index+3).width = 2000

                worksheet.write(row_index, last_col_index+3, item.get('payCount'), style)
                worksheet.col(last_col_index+3).width = 2000

                row_index = row_index+1
        else:
            row_index = row_index+1
    doc = CompoundDoc.XlsDoc()
    stream = workbook.get_biff_data()
    padding = b'\x00' * (0x1000 - (len(stream) % 0x1000))
    doc.book_stream_len = len(stream) + len(padding)
    doc._build_directory()
    doc._build_sat()
    doc._build_header()
    # 拼接xls文件字节
    data = doc.header+doc.packed_MSAT_1st+stream+padding+doc.packed_MSAT_2nd+doc.packed_SAT+doc.dir_stream
    fileName = worksheet.name + getFormatTime('%Y%m%d%H%M%S') + random_num(6) + '.xlsx'
    bucket.put_object(filePath + fileName, data)

    return baseImgUrl + filePath + fileName